/*
 * SermonUploaderView.java
 */

package sermonuploader;

import java.awt.Cursor;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.jdesktop.application.SingleFrameApplication;
import org.jdesktop.application.FrameView;
import javax.swing.JDialog;
import javax.swing.JFileChooser;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;

import java.sql.*;
import java.util.Calendar;
import java.util.Properties;
import javax.swing.JOptionPane;
import javax.swing.filechooser.FileFilter;


/**
 * The application's main frame.
 */
public class SermonUploaderView extends FrameView {

    String db_url;
    String db_username;
    String db_password;
    String ftp_url;
    String ftp_username;
    String ftp_password;
    String ftp_path;
    String teacher_table;
    String series_table;
    String study_table;
    String venue_table;
    String files_table;
    
    public SermonUploaderView(SingleFrameApplication app) {
        super(app);
        try {
            Properties configFile = new Properties();
            ClassLoader cl = this.getClass().getClassLoader();
            InputStream is = cl.getResourceAsStream("./sermonuploader/resources/sermonUploader.properties");
            configFile.load(is);
                db_url = configFile.getProperty("DB_URL");
                db_username = configFile.getProperty("DB_USERNAME");
                db_password = configFile.getProperty("DB_PASSWORD");
                ftp_url = configFile.getProperty("FTP_SERVER");
                ftp_username = configFile.getProperty("FTP_USERNAME");
                ftp_password = configFile.getProperty("FTP_PASSWORD");
                ftp_path = configFile.getProperty("FTP_PATH");
                teacher_table=configFile.getProperty("TEACHER_TABLE");
                series_table=configFile.getProperty("SERIES_TABLE");
                venue_table=configFile.getProperty("LOCATION_TABLE");
                study_table=configFile.getProperty("STUDY_TABLE");
                files_table=configFile.getProperty("FILE_TABLE");
                initComponents();
                JOptionPane.showMessageDialog(null,"Connecting to database.\nApp window will appear once connection is established.\nPlease be patient ;0)\nClick OK to continue","Connecting",JOptionPane.PLAIN_MESSAGE);
                connect();
                populateCombos(true);
        } catch (IOException ex) {
            Logger.getLogger(SermonUploaderView.class.getName()).log(Level.SEVERE, null, ex);
        }
            

    }

    public void connect()
    {
        
        try {
            
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String url = db_url;
            String username = db_username;
            String password = db_password;
             
            conn = DriverManager.getConnection(url, username, password);
            if (!conn.isClosed()) {
                System.out.println("Successfully connected to MySQL server...");
            } else {
                System.out.println("Massive failure, please try again");
                JOptionPane.showMessageDialog(null,"Connection failed, please quit the application and try again.\nIf this does not work please check your internet connection.","Connection failed!",JOptionPane.ERROR_MESSAGE);
                System.exit(0);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SermonUploaderView.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(SermonUploaderView.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(SermonUploaderView.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(SermonUploaderView.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void populateCombos(boolean firsttime)
    {
        try {
            mainPanel.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery("SELECT teachername,id FROM "+teacher_table+" ORDER BY teachername ASC");
            int size =0;
            if (rs != null)
            {
                rs.beforeFirst();
                rs.last();
                size = rs.getRow();
                rs.beforeFirst();
            }

            speakerIDs=new int[size];
            int counter=0;
            while(rs.next())
            {
                speaker_combo.addItem(rs.getString(1));
                speakerIDs[counter]=rs.getInt("id");
                counter++;
            }
            speaker_combo.setSelectedIndex(-1);
            rs = s.executeQuery("SELECT location_text,id FROM "+venue_table);
            if (rs != null)
            {
                rs.beforeFirst();
                rs.last();
                size = rs.getRow();
                rs.beforeFirst();
            }
            venueIDs=new int[size];
            counter=0;
            while(rs.next())
            {
                venue_combo.addItem(rs.getString(1));
                venueIDs[counter]=rs.getInt(2);
                counter++;
            }
            venue_combo.setSelectedIndex(0);
            if(firsttime)
            {
                Calendar g =Calendar.getInstance();
                year_combo.setSelectedIndex(g.get(Calendar.YEAR)-2005);
                month_combo.setSelectedIndex(g.get(Calendar.MONTH));
                day_combo.setSelectedIndex(g.get(Calendar.DATE)-1);
            }

            rs = s.executeQuery("SELECT series_text,id FROM "+series_table);
            if (rs != null)
            {
                rs.beforeFirst();
                rs.last();
                size = rs.getRow();
                rs.beforeFirst();
            }
            seriesIDs=new int[size];
            counter=0;
            while(rs.next())
            {
                series_combo.addItem(rs.getString(1));
                seriesIDs[counter]=rs.getInt(2);
                counter++;
            }
            mainPanel.setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
        } catch (SQLException ex) {
            Logger.getLogger(SermonUploaderView.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        mainPanel = new javax.swing.JPanel();
        jPanel1 = new javax.swing.JPanel();
        file_panel = new javax.swing.JPanel();
        jLabel1 = new javax.swing.JLabel();
        file_box = new javax.swing.JTextField();
        file_btn = new javax.swing.JButton();
        title_panel = new javax.swing.JPanel();
        jLabel2 = new javax.swing.JLabel();
        title_box = new javax.swing.JTextField();
        date_panel = new javax.swing.JPanel();
        jLabel3 = new javax.swing.JLabel();
        year_combo = new javax.swing.JComboBox();
        month_combo = new javax.swing.JComboBox();
        day_combo = new javax.swing.JComboBox();
        time_panel = new javax.swing.JPanel();
        jLabel4 = new javax.swing.JLabel();
        am_btn = new javax.swing.JRadioButton();
        pm_btn = new javax.swing.JRadioButton();
        speaker_panel = new javax.swing.JPanel();
        jLabel5 = new javax.swing.JLabel();
        speaker_combo = new javax.swing.JComboBox();
        new_spkr_btn = new javax.swing.JButton();
        venue_panel = new javax.swing.JPanel();
        jLabel6 = new javax.swing.JLabel();
        venue_combo = new javax.swing.JComboBox();
        new_venue_btn = new javax.swing.JButton();
        series_panel = new javax.swing.JPanel();
        jLabel7 = new javax.swing.JLabel();
        series_combo = new javax.swing.JComboBox();
        new_series_btn = new javax.swing.JButton();
        jPanel2 = new javax.swing.JPanel();
        upload_btn = new javax.swing.JButton();
        progbar = new javax.swing.JProgressBar();
        menuBar = new javax.swing.JMenuBar();
        javax.swing.JMenu fileMenu = new javax.swing.JMenu();
        javax.swing.JMenuItem exitMenuItem = new javax.swing.JMenuItem();
        buttonGroup1 = new javax.swing.ButtonGroup();
        chooser = new javax.swing.JFileChooser();

        mainPanel.setName("mainPanel"); // NOI18N

        jPanel1.setBorder(javax.swing.BorderFactory.createEtchedBorder());
        jPanel1.setName("jPanel1"); // NOI18N
        jPanel1.setLayout(new java.awt.GridLayout(7, 1));

        file_panel.setName("file_panel"); // NOI18N
        file_panel.setLayout(new java.awt.FlowLayout(java.awt.FlowLayout.LEFT));

        jLabel1.setHorizontalAlignment(javax.swing.SwingConstants.LEFT);
        org.jdesktop.application.ResourceMap resourceMap = org.jdesktop.application.Application.getInstance(sermonuploader.SermonUploaderApp.class).getContext().getResourceMap(SermonUploaderView.class);
        jLabel1.setText(resourceMap.getString("jLabel1.text")); // NOI18N
        jLabel1.setName("jLabel1"); // NOI18N
        file_panel.add(jLabel1);

        file_box.setText(resourceMap.getString("file_box.text")); // NOI18N
        file_box.setEnabled(false);
        file_box.setName("file_box"); // NOI18N
        file_box.setPreferredSize(new java.awt.Dimension(200, 20));
        file_panel.add(file_box);

        file_btn.setText(resourceMap.getString("file_btn.text")); // NOI18N
        file_btn.setName("file_btn"); // NOI18N
        file_btn.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                file_btnMouseClicked(evt);
            }
        });
        file_panel.add(file_btn);

        jPanel1.add(file_panel);

        title_panel.setName("title_panel"); // NOI18N
        title_panel.setLayout(new java.awt.FlowLayout(java.awt.FlowLayout.LEFT));

        jLabel2.setText(resourceMap.getString("jLabel2.text")); // NOI18N
        jLabel2.setName("jLabel2"); // NOI18N
        title_panel.add(jLabel2);

        title_box.setText(resourceMap.getString("title_box.text")); // NOI18N
        title_box.setMinimumSize(new java.awt.Dimension(50, 20));
        title_box.setName("title_box"); // NOI18N
        title_box.setPreferredSize(new java.awt.Dimension(300, 20));
        title_box.addInputMethodListener(new java.awt.event.InputMethodListener() {
            public void caretPositionChanged(java.awt.event.InputMethodEvent evt) {
            }
            public void inputMethodTextChanged(java.awt.event.InputMethodEvent evt) {
                title_boxInputMethodTextChanged(evt);
            }
        });
        title_panel.add(title_box);

        jPanel1.add(title_panel);

        date_panel.setName("date_panel"); // NOI18N
        date_panel.setLayout(new java.awt.FlowLayout(java.awt.FlowLayout.LEFT));

        jLabel3.setText(resourceMap.getString("jLabel3.text")); // NOI18N
        jLabel3.setName("jLabel3"); // NOI18N
        date_panel.add(jLabel3);

        year_combo.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "2005", "2006", "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015", "2016", "2017", "2018", "2019", "2020" }));
        year_combo.setSelectedIndex(5);
        year_combo.setName("year_combo"); // NOI18N
        year_combo.setPreferredSize(new java.awt.Dimension(90, 20));
        date_panel.add(year_combo);

        month_combo.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" }));
        month_combo.setName("month_combo"); // NOI18N
        month_combo.setPreferredSize(new java.awt.Dimension(90, 20));
        date_panel.add(month_combo);

        day_combo.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31" }));
        day_combo.setName("day_combo"); // NOI18N
        day_combo.setPreferredSize(new java.awt.Dimension(80, 20));
        date_panel.add(day_combo);

        jPanel1.add(date_panel);

        time_panel.setName("time_panel"); // NOI18N
        time_panel.setLayout(new java.awt.FlowLayout(java.awt.FlowLayout.LEFT));

        jLabel4.setText(resourceMap.getString("jLabel4.text")); // NOI18N
        jLabel4.setName("jLabel4"); // NOI18N
        time_panel.add(jLabel4);

        buttonGroup1.add(am_btn);
        am_btn.setSelected(true);
        am_btn.setText(resourceMap.getString("am_btn.text")); // NOI18N
        am_btn.setName("am_btn"); // NOI18N
        time_panel.add(am_btn);

        buttonGroup1.add(pm_btn);
        pm_btn.setText(resourceMap.getString("pm_btn.text")); // NOI18N
        pm_btn.setName("pm_btn"); // NOI18N
        time_panel.add(pm_btn);

        jPanel1.add(time_panel);

        speaker_panel.setName("speaker_panel"); // NOI18N
        speaker_panel.setLayout(new java.awt.FlowLayout(java.awt.FlowLayout.LEFT));

        jLabel5.setText(resourceMap.getString("jLabel5.text")); // NOI18N
        jLabel5.setName("jLabel5"); // NOI18N
        speaker_panel.add(jLabel5);

        speaker_combo.setName("speaker_combo"); // NOI18N
        speaker_combo.setPreferredSize(new java.awt.Dimension(200, 20));
        speaker_panel.add(speaker_combo);

        new_spkr_btn.setText(resourceMap.getString("new_spkr_btn.text")); // NOI18N
        new_spkr_btn.setName("new_spkr_btn"); // NOI18N
        new_spkr_btn.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                new_spkr_btnMouseClicked(evt);
            }
        });
        speaker_panel.add(new_spkr_btn);

        jPanel1.add(speaker_panel);

        venue_panel.setName("venue_panel"); // NOI18N
        venue_panel.setLayout(new java.awt.FlowLayout(java.awt.FlowLayout.LEFT));

        jLabel6.setText(resourceMap.getString("jLabel6.text")); // NOI18N
        jLabel6.setName("jLabel6"); // NOI18N
        venue_panel.add(jLabel6);

        venue_combo.setName("venue_combo"); // NOI18N
        venue_combo.setPreferredSize(new java.awt.Dimension(150, 20));
        venue_panel.add(venue_combo);

        new_venue_btn.setText(resourceMap.getString("new_venue_btn.text")); // NOI18N
        new_venue_btn.setName("new_venue_btn"); // NOI18N
        new_venue_btn.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                new_venue_btnMouseClicked(evt);
            }
        });
        venue_panel.add(new_venue_btn);

        jPanel1.add(venue_panel);

        series_panel.setName("series_panel"); // NOI18N
        series_panel.setLayout(new java.awt.FlowLayout(java.awt.FlowLayout.LEFT));

        jLabel7.setText(resourceMap.getString("jLabel7.text")); // NOI18N
        jLabel7.setName("jLabel7"); // NOI18N
        series_panel.add(jLabel7);

        series_combo.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "None" }));
        series_combo.setName("series_combo"); // NOI18N
        series_combo.setPreferredSize(new java.awt.Dimension(150, 20));
        series_panel.add(series_combo);

        new_series_btn.setText(resourceMap.getString("new_series_btn.text")); // NOI18N
        new_series_btn.setName("new_series_btn"); // NOI18N
        new_series_btn.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                new_series_btnMouseClicked(evt);
            }
        });
        series_panel.add(new_series_btn);

        jPanel1.add(series_panel);

        mainPanel.add(jPanel1);

        jPanel2.setBorder(javax.swing.BorderFactory.createEtchedBorder());
        jPanel2.setName("jPanel2"); // NOI18N
        jPanel2.setLayout(new java.awt.GridLayout(2, 0));

        upload_btn.setFont(resourceMap.getFont("upload_btn.font")); // NOI18N
        upload_btn.setText(resourceMap.getString("upload_btn.text")); // NOI18N
        upload_btn.setName("upload_btn"); // NOI18N
        upload_btn.setPreferredSize(new java.awt.Dimension(300, 37));
        upload_btn.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                upload_btnMouseClicked(evt);
            }
        });
        jPanel2.add(upload_btn);

        progbar.setName("progbar"); // NOI18N
        progbar.setPreferredSize(new java.awt.Dimension(300, 19));
        jPanel2.add(progbar);

        mainPanel.add(jPanel2);

        menuBar.setName("menuBar"); // NOI18N

        fileMenu.setText(resourceMap.getString("fileMenu.text")); // NOI18N
        fileMenu.setName("fileMenu"); // NOI18N

        javax.swing.ActionMap actionMap = org.jdesktop.application.Application.getInstance(sermonuploader.SermonUploaderApp.class).getContext().getActionMap(SermonUploaderView.class, this);
        exitMenuItem.setAction(actionMap.get("quit")); // NOI18N
        exitMenuItem.setName("exitMenuItem"); // NOI18N
        exitMenuItem.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                exitMenuItemMouseClicked(evt);
            }
        });
        fileMenu.add(exitMenuItem);

        menuBar.add(fileMenu);

        chooser.setAcceptAllFileFilterUsed(false);
        FileFilter filter = new FileFilter()
        {
            public boolean accept(File file)
            {
                String filename = file.toString();
                String ext = filename.substring((filename.lastIndexOf("."))+1, filename.length());
                return (ext.equals("mp3")||file.isDirectory());
            }
            public String getDescription()
            {
                return "mp3 files";
            }
        };

        chooser.addChoosableFileFilter(filter);
        chooser.setName("chooser"); // NOI18N

        setComponent(mainPanel);
    }// </editor-fold>//GEN-END:initComponents

    private void exitMenuItemMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_exitMenuItemMouseClicked
        try {
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(SermonUploaderView.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.exit(0);
    }//GEN-LAST:event_exitMenuItemMouseClicked

    private void cleverFilenameParser(String filename)
    {
        if(filename.charAt(0)=='2')
        {
            String year = filename.substring(0,4);
            String month = filename.substring(4,6);
            String day = filename.substring(6, 8);
            String time = filename.substring(8, 10);

            year_combo.setSelectedItem(year);
            if(filename.charAt(4)=='0'||filename.charAt(4)=='1')
                month_combo.setSelectedIndex(Integer.parseInt(month)-1);
            if(filename.charAt(6)=='0'||filename.charAt(6)=='1'||filename.charAt(6)=='2'||filename.charAt(6)=='3')
                day_combo.setSelectedItem(day);
            if(time.charAt(0)=='A'||time.charAt(0)=='a')
                am_btn.setSelected(true);
            if(time.charAt(0)=='P'||time.charAt(0)=='p')
                pm_btn.setSelected(true);
            if(filename.charAt(10)=='-')
            {
                String speaker = filename.substring(11,filename.indexOf("-", 12));
                speaker_combo.setSelectedItem(speaker);
                String title = filename.substring(filename.indexOf("-", 12)+1, filename.lastIndexOf("."));
                title_box.setText(title);
            }
        }

    }

    private void file_btnMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_file_btnMouseClicked
        int option = chooser.showOpenDialog(null);
        if(option!=JFileChooser.CANCEL_OPTION)
        {
            if(option==JFileChooser.APPROVE_OPTION)
            {
                file=chooser.getSelectedFile();
            }
            file_box.setText(file.getPath());
            cleverFilenameParser(file.getName());
        }
        upload_btn.setEnabled(true);
    }//GEN-LAST:event_file_btnMouseClicked

    private int sanityCheck()
    {
        int ok=0;
        if(file_box.getText().length()==0)
            ok+=1;
        if(title_box.getText().length()==0)
            ok+=2;
        
        return ok;
    }

    // I'm 99% sure this method is old, and the new Uploader class is called in it's place.
    // Still need to verify this though.
    private void upload(String fileName) throws MalformedURLException,
         IOException
    {
        String ftpServer=ftp_url;
        String user=ftp_username;
        String password=ftp_password;
        File source = file;

        if (ftpServer != null && fileName != null && source != null)
      {
         StringBuffer sb = new StringBuffer( "ftp://" );
         // check for authentication else assume its anonymous access.
         if (user != null && password != null)
         {
            sb.append( user );
            sb.append( ':' );
            sb.append( password );
            sb.append( '@' );
         }
         sb.append( ftpServer );
         sb.append( ftp_path );
         sb.append( fileName );
         /*
          * type ==> a=ASCII mode, i=image (binary) mode, d= file directory
          * listing
          */
         sb.append( ";type=i" );

         BufferedInputStream bis = null;
         BufferedOutputStream bos = null;
         try
         {
            URL url = new URL( sb.toString() );
            URLConnection urlc = url.openConnection();

            bos = new BufferedOutputStream( urlc.getOutputStream() );
            bis = new BufferedInputStream( new FileInputStream( source ) );
           

            int i;
            // read byte by byte until end of stream
            
            
            while ((i = bis.read()) != -1)
            {
               
               bos.write( i );
            }
         }
         finally
         {
            if (bis != null)
               try
               {
                  bis.close();
                 
               }
               catch (IOException ioe)
               {
                 
                  ioe.printStackTrace();
               }
            if (bos != null)
               try
               {
                  bos.close();
                  
               }
               catch (IOException ioe)
               {
                  JOptionPane.showMessageDialog(null,"File upload failed. Please try again.","Upload failed", JOptionPane.ERROR_MESSAGE);
                  ioe.printStackTrace();
               }
         }
      }
      else
      {
         JOptionPane.showMessageDialog(null,"File upload failed. Please try again.","Upload failed", JOptionPane.ERROR_MESSAGE);
         System.out.println( "Input not available." );
      }

    }

    private int checkNames(String name)
    {
        int pos=-1;
        for(int j=0;j<speaker_combo.getItemCount();j++)
        {
            if(name.equalsIgnoreCase((String)speaker_combo.getItemAt(j)))
            {
                pos=j;
                System.out.println(pos);
            }
        }
        return pos;
    }

    private void upload_btnMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_upload_btnMouseClicked
        int sanity = sanityCheck();
        if(sanity==0)
        {
            int month = month_combo.getSelectedIndex()+1;
            String month_str = String.valueOf(month);
            if (month<10)
                month_str="0"+month_str;
            String newFilename = year_combo.getSelectedItem().toString()+month_str+day_combo.getSelectedItem().toString();
            if(am_btn.isSelected())
                newFilename+="AM";
            else
                newFilename+="PM";
            String title = title_box.getText().replace("'", String.valueOf((char)96));
            title.replace('\"', (char)96);
            String newTitle = title_box.getText().replace(' ','_');
            newTitle = newTitle.replace('\'', '_');
            newTitle = newTitle.replace('\"', '_');
            String newSpeaker = speaker_combo.getSelectedItem().toString().replace(' ','_');
            newFilename += "-"+newSpeaker+"-"+newTitle+".mp3";
            System.out.println(newFilename);
            int teacherID=0, venueID=0, seriesID=0;
            
            try {
                upload_btn.setEnabled(false);
                //uploadfile

                Uploader up = new Uploader(newFilename,file,progbar);
                up.start();
                              
                  // Get IDs and date/time
                //date
                String date = year_combo.getSelectedItem().toString()+"-"+month_str+"-"+day_combo.getSelectedItem().toString();
                //time
                String time;
                if(am_btn.isSelected())
                    time="10:00:00";
                else
                    time="19:00:00";
                //teacherID
                teacherID = speakerIDs[speaker_combo.getSelectedIndex()];
                //venueID
                venueID=venueIDs[venue_combo.getSelectedIndex()];
                //seriesID
                if(series_combo.getSelectedIndex()!=0)
                        seriesID=seriesIDs[series_combo.getSelectedIndex()-1];
                    else
                        seriesID=0;

                // edit studies
                int recordsUpdated;
                Statement st = conn.createStatement();
                recordsUpdated = st.executeUpdate("INSERT INTO "+study_table+" (`id`, `studydate`, `teacher_id`, `studynumber`, `booknumber`, `chapter_begin`, `verse_begin`, `chapter_end`, `secondary_reference`, `booknumber2`, `chapter_begin2`, `verse_begin2`, `chapter_end2`, `verse_end2`, `prod_dvd`, `prod_cd`, `server_cd`, `server_dvd`, `image_cd`, `image_dvd`, `studytext2`, `comments`, `hits`, `user_id`, `user_name`, `show_level`, `location_id`, `verse_end`, `studytitle`, `studyintro`, `media_hours`, `media_minutes`, `media_seconds`, `messagetype`, `series_id`, `topics_id`, `studytext`, `thumbnailm`, `thumbhm`, `thumbwm`, `params`, `published`) VALUES (NULL, '"+date+" "+time+"','"+teacherID+"', '', '0', '0', '0', '0', '', '0', '', '', '', '', NULL, NULL, NULL, NULL, NULL, '0', NULL, '1', '0', '62', 'Administrator', '0', '"+venueID+"', '0', '"+title+"', '', '', '', '', '1', '"+seriesID+"', '0', '', '', NULL, NULL, NULL, '1');");
                //studyID
                ResultSet rs = st.executeQuery("SELECT id from "+study_table+" where (studytitle='"+title+"' AND studydate='"+date+" "+time+"')");
                rs.first();
                int studyID=rs.getInt("id");

                //edit mediafiles
                st = conn.createStatement();
                recordsUpdated = st.executeUpdate("INSERT INTO "+files_table+" (`id`, `study_id`, `media_image`, `server`, `path`, `special`, `filename`, `size`, `mime_type`, `podcast_id`, `internal_viewer`, `mediacode`, `ordering`, `createdate`, `link_type`, `hits`, `published`, `docMan_id`, `article_id`, `comment`, `virtueMart_id`, `params`) VALUES (NULL, "+studyID+", '2', '1', '1', '', '"+newFilename+"', NULL, '1', '1', '0', '{mp3}"+newFilename+"{/mp3}', '0', '"+date+" "+time+"', '1', NULL, '1', '0', '0', NULL, '0', 'player=1')");
                
            } catch (SQLException ex) {
                Logger.getLogger(SermonUploaderView.class.getName()).log(Level.SEVERE, null, ex);
            
            }
        }
        else
        {
            //display problem box
            String problem="";
            if(sanity%2==1)
                problem+="Please choose a file to upload\n";
            sanity/=2;
            if(sanity%2==1)
                problem+="Please enter a title\n";
           
            JOptionPane.showMessageDialog(null,problem,"Error",JOptionPane.ERROR_MESSAGE);
                
        }
    }//GEN-LAST:event_upload_btnMouseClicked

    private void title_boxInputMethodTextChanged(java.awt.event.InputMethodEvent evt) {//GEN-FIRST:event_title_boxInputMethodTextChanged
        
    }//GEN-LAST:event_title_boxInputMethodTextChanged

    private void new_spkr_btnMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_new_spkr_btnMouseClicked
        try {
            String newSpkrName = (String) JOptionPane.showInputDialog(null,"Enter new speaker's name:", "New Speaker", JOptionPane.PLAIN_MESSAGE);
            if(newSpkrName!=null)
            {
                int recordsUpdated;
                Statement st = conn.createStatement();
                recordsUpdated = st.executeUpdate("INSERT INTO "+teacher_table+" (`id`, `teacher_image`, `teacher_thumbnail`, `teachername`, `title`, `phone`, `email`, `website`, `information`, `image`, `imageh`, `imagew`, `thumb`, `thumbw`, `thumbh`, `short`, `ordering`, `catid`, `list_show`, `published`) VALUES (NULL, NULL, NULL, '" + newSpkrName + "', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', '1', '1')");
                int venue = venue_combo.getSelectedIndex();
                int series = series_combo.getSelectedIndex();
                speaker_combo.removeAllItems();
                venue_combo.removeAllItems();
                series_combo.removeAllItems();
                series_combo.addItem("None");
                populateCombos(false);
                venue_combo.setSelectedIndex(venue);
                series_combo.setSelectedIndex(series);
                speaker_combo.setSelectedItem(newSpkrName);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SermonUploaderView.class.getName()).log(Level.SEVERE, null, ex);
        }
    }//GEN-LAST:event_new_spkr_btnMouseClicked

    private void new_venue_btnMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_new_venue_btnMouseClicked
        try {
            String newVenue = (String) JOptionPane.showInputDialog(null,"Enter new venue:", "New Venue", JOptionPane.PLAIN_MESSAGE);
            if(newVenue!=null)
            {
                int recordsUpdated;
                Statement st = conn.createStatement();
                recordsUpdated = st.executeUpdate("INSERT INTO "+venue_table+" (`id`, `location_text`, `published`) VALUES (NULL, '" + newVenue + "', '1');");

                int spkr=speaker_combo.getSelectedIndex();
                int series=series_combo.getSelectedIndex();
                venue_combo.removeAllItems();
                speaker_combo.removeAllItems();
                series_combo.removeAllItems();
                series_combo.addItem("None");
                populateCombos(false);
                speaker_combo.setSelectedIndex(spkr);
                series_combo.setSelectedIndex(series);
                venue_combo.setSelectedItem(newVenue);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SermonUploaderView.class.getName()).log(Level.SEVERE, null, ex);
        }

    }//GEN-LAST:event_new_venue_btnMouseClicked

    private void new_series_btnMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_new_series_btnMouseClicked
        try {
            String newSeries = (String) JOptionPane.showInputDialog(null,"Enter new series name:", "New Series", JOptionPane.PLAIN_MESSAGE);
            if(newSeries!=null)
            {
                int recordsUpdated;
                Statement st = conn.createStatement();
                recordsUpdated = st.executeUpdate("INSERT INTO "+series_table+" (`id`, `series_text`, `teacher`, `description`, `series_thumbnail`, `published`) VALUES (NULL, '" + newSeries + "', NULL, NULL, NULL, '1');");

                int spkr=speaker_combo.getSelectedIndex();
                int venue = venue_combo.getSelectedIndex();
                series_combo.removeAllItems();
                series_combo.addItem("None");
                speaker_combo.removeAllItems();
                venue_combo.removeAllItems();

                populateCombos(false);
                speaker_combo.setSelectedIndex(spkr);
                venue_combo.setSelectedIndex(venue);
                series_combo.setSelectedItem(newSeries);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SermonUploaderView.class.getName()).log(Level.SEVERE, null, ex);
        }

    }//GEN-LAST:event_new_series_btnMouseClicked

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JRadioButton am_btn;
    private javax.swing.ButtonGroup buttonGroup1;
    private javax.swing.JFileChooser chooser;
    private javax.swing.JPanel date_panel;
    private javax.swing.JComboBox day_combo;
    private javax.swing.JTextField file_box;
    private javax.swing.JButton file_btn;
    private javax.swing.JPanel file_panel;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JLabel jLabel7;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JPanel mainPanel;
    private javax.swing.JMenuBar menuBar;
    private javax.swing.JComboBox month_combo;
    private javax.swing.JButton new_series_btn;
    private javax.swing.JButton new_spkr_btn;
    private javax.swing.JButton new_venue_btn;
    private javax.swing.JRadioButton pm_btn;
    private javax.swing.JProgressBar progbar;
    private javax.swing.JComboBox series_combo;
    private javax.swing.JPanel series_panel;
    private javax.swing.JComboBox speaker_combo;
    private javax.swing.JPanel speaker_panel;
    private javax.swing.JPanel time_panel;
    private javax.swing.JTextField title_box;
    private javax.swing.JPanel title_panel;
    private javax.swing.JButton upload_btn;
    private javax.swing.JComboBox venue_combo;
    private javax.swing.JPanel venue_panel;
    private javax.swing.JComboBox year_combo;
    // End of variables declaration//GEN-END:variables
    private Connection conn;
    private File file;
    private int[] speakerIDs;
    private int[] venueIDs;
    private int[] seriesIDs;

    private JDialog aboutBox;
}
